﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlUtilz
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(cmbDatabase.Text))
            {

                btnStartAsyncOperation.Enabled = button2.Enabled = false;

                btnCancel.Enabled = true;
                WorkerParams objWorkerParams = new WorkerParams();
                objWorkerParams.ConnectionString = "Data Source=" + txtServerName.Text + ";Initial Catalog=" + cmbDatabase.Text + ";" + "User id=" + txtUserName.Text + ";Password=" + txtPassword.Text + ";";
                objWorkerParams.FolderPath = txtFolder.Text;
                bgWork.RunWorkerAsync(objWorkerParams);
            }
            else
            {
                MessageBox.Show("Select database.", "-database-");
                cmbDatabase.Focus();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void bgWork_DoWork(object sender, DoWorkEventArgs e)
        {

            try
            {
                string procedurecontent = "";
                WorkerParams objWorkerParams = e.Argument as WorkerParams;
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = objWorkerParams.ConnectionString;
                conn.Open();
                conn.Close();

                SqlDataAdapter adpt = new SqlDataAdapter("SELECT name FROM sys.procedures", conn);
                DataTable dtt = new DataTable();
                adpt.Fill(dtt);
                Int32 i = 0, Count = dtt.Rows.Count;
                foreach (DataRow item in dtt.Rows)
                {
                    i++;
                    bgWork.ReportProgress(i * 100 / Count);
                    if (bgWork.CancellationPending)
                    {
                        e.Cancel = true;
                        bgWork.ReportProgress(0);
                        return;
                    }
                    procedurecontent = "";
                    using (SqlConnection myConn = new SqlConnection(objWorkerParams.ConnectionString))
                    {
                        using (SqlCommand command = new SqlCommand("sp_helptext @ProcName", myConn))
                        {
                            command.Parameters.AddWithValue("@ProcName", "[" + item[0] + "]");
                            myConn.Open();
                            using (SqlDataReader myReader = command.ExecuteReader())
                            {
                                if (myReader.HasRows)
                                {
                                    while (myReader.Read())
                                    {
                                        procedurecontent += myReader["Text"].ToString();
                                    }
                                    File.WriteAllText(objWorkerParams.FolderPath + @"/dbo." + item[0] + ".Sql", procedurecontent.Trim());
                                }

                            }
                            myConn.Close();
                        }
                    }

                }


            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.ToString(), "Failed to connect.");
                throw ex;
            }
        }

        private void bgWork_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar1.Value = e.ProgressPercentage;
            lblStatus.Text = "Status: Processing......" + progressBar1.Value.ToString() + "%";
        }

        private void bgWork_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Cancelled)
            {
                lblStatus.Text = "Status: Task Cancelled.";
            }

              // Check to see if an error occurred in the background process.

            else if (e.Error != null)
            {
                lblStatus.Text = "Error while performing background operation.";
            }
            else
            {
                // Everything completed normally.
                lblStatus.Text = "Status: Task Completed...";
            }

            //Change the status of the buttons on the UI accordingly
            btnStartAsyncOperation.Enabled = button2.Enabled = true;
            btnCancel.Enabled = false;
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {

            if (bgWork.IsBusy)
            {
                bgWork.CancelAsync();
            }
        }

        private void comboBox1_Enter(object sender, EventArgs e)
        {
            string AppConection = "Data Source=" + txtServerName.Text + ";Initial Catalog=" + cmbDatabase.Text + ";" + "User id=" + txtUserName.Text + ";Password=" + txtPassword.Text + ";";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = AppConection;
            SqlDataAdapter adpt = new SqlDataAdapter("SELECT name FROM master.dbo.sysdatabases", conn);
            DataTable dtt = new DataTable();
            adpt.Fill(dtt);
            cmbDatabase.DataSource = dtt;
            cmbDatabase.DisplayMember = "name";
            cmbDatabase.ValueMember = "name";
            cmbDatabase.DroppedDown = true;
        }

        private void lblStatus_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            MessageBox.Show("Sandeep;");
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            txtFolder.Text = Application.StartupPath;
        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            FolderBrowserDialog objFolder = new FolderBrowserDialog();
            objFolder.SelectedPath = txtFolder.Text;
            if (objFolder.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                txtFolder.Text = objFolder.SelectedPath;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (Directory.Exists(txtFolder.Text))
            {
                ProcessStartInfo startInfo = new ProcessStartInfo
                {
                    Arguments = txtFolder.Text,
                    FileName = "explorer.exe"
                };

                Process.Start(startInfo);
            }
        }
    }
    public class WorkerParams
    {
        public string ConnectionString { get; set; }
        public string FolderPath { get; set; }
    }
}
